import pandas as pd
import joblib
from sqlalchemy import create_engine
from datetime import date

# ✅ 1. 数据库配置
from db_config import user, password, host, port, database

engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}')

# ✅ 2. 特征字段列表（保持与训练一致）
features = [
    'total_views', 'avg_stay_ms_per_view', 'comment_count', 'avg_score',
    'reward_total', 'sales_amount', 'contact_count', 'share_count',
    'recent_7d_views', 'recent_7d_stay_ms', 'recent_7d_comments',
    'previous_7d_views', 'previous_7d_stay_ms', 'days_since_register', 'active_days'
]


# ✅ 3. 获取用于预测的SQL封装
def get_user_features(engine, target_date=None):
    if target_date is None:
        target_date = date.today().isoformat()

    sql = f"""
    WITH user_activity_days AS (
        SELECT platform_id, platform, COUNT(DISTINCT create_date) AS active_days
        FROM tra_user_date_summary
        WHERE create_date < DATE('{target_date}') AND platform_id > 0
        GROUP BY platform_id, platform
    )

    SELECT 
        u.platform_id,
        u.platform,
        u.view_count AS total_views,
        IFNULL(u.stay_ms / NULLIF(u.view_count, 0), 0) AS avg_stay_ms_per_view,
        u.comment_count,
        u.avg_score,
        u.reward_total,
        u.sales_amount,
        u.contact_count,
        u.share_count,
        ifnull(recent.view_count,0) AS recent_7d_views,
        ifnull(recent.stay_ms,0) AS recent_7d_stay_ms,
        ifnull(recent.comment_count,0) AS recent_7d_comments,
        ifnull(old.view_count,0) AS previous_7d_views,
        ifnull(old.stay_ms,0) AS previous_7d_stay_ms,
        DATEDIFF(DATE('{target_date}'), u.create_time) AS days_since_register,
        a.active_days,
        CASE 
            WHEN DATEDIFF(DATE('{target_date}'), u.create_time) <= 14 AND a.active_days <= 3 THEN 'cold_start'
            WHEN (DATEDIFF(DATE('{target_date}'), u.create_time) BETWEEN 15 AND 60 OR a.active_days BETWEEN 4 AND 20) THEN 'growth'
            WHEN DATEDIFF(DATE('{target_date}'), u.create_time) > 60 AND a.active_days >= 21 THEN 'mature'
            ELSE 'unknown'
        END AS user_lifecycle_segment
    FROM tra_user_summary u
    JOIN user_activity_days a 
        ON u.platform_id = a.platform_id AND u.platform = a.platform
    LEFT JOIN (
        SELECT platform_id, platform, 
               SUM(view_count) AS view_count, 
               SUM(stay_ms) AS stay_ms,
               SUM(comment_count) AS comment_count
        FROM tra_user_date_summary
        WHERE create_date BETWEEN DATE_SUB(DATE('{target_date}'), INTERVAL 6 DAY) AND DATE('{target_date}')
        GROUP BY platform_id, platform
    ) recent ON u.platform_id = recent.platform_id AND u.platform = recent.platform
    LEFT JOIN (
        SELECT platform_id, platform, 
               SUM(view_count) AS view_count, 
               SUM(stay_ms) AS stay_ms
        FROM tra_user_date_summary
        WHERE create_date BETWEEN DATE_SUB(DATE('{target_date}'), INTERVAL 13 DAY) 
                              AND DATE_SUB(DATE('{target_date}'), INTERVAL 7 DAY)
        GROUP BY platform_id, platform
    ) old ON u.platform_id = old.platform_id AND u.platform = old.platform
    WHERE a.active_days >= 7;
    """
    return pd.read_sql(sql, engine)


# ✅ 4. 主预测流程
def predict_churn(target_date=None):
    # 加载用户特征数据
    df = get_user_features(engine, target_date)

    # 取模型需要的字段
    X = df[features]

    # 加载模型（确保路径正确）
    model = joblib.load("data/logistic_model.pkl")

    # 预测概率和标签
    churn_probs = model.predict_proba(X)[:, 1]
    churn_preds = model.predict(X)

    # 拼接结果
    df['churn_prob'] = churn_probs
    df['churn_pred'] = churn_preds

    # 可选保存
    df.to_csv("data/churn_predictions.csv", index=False)

    return df[['platform_id', 'platform', 'churn_prob', 'churn_pred']]


# ✅ 调用
if __name__ == '__main__':
    result = predict_churn()
    print(result.head())
